package ca.uvic.group2.server;

import ca.uvic.group2.api.*;
import ca.uvic.group2.api.exceptions.*;
import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.*;
import org.apache.log4j.*;



//DBConn class
public class DBConnection2 {

    private static Category debugLog = Logger.getInstance("DBConnDebug");
    private static Category perfLog = Logger.getInstance("DBConnPerf");

    static final String SERVER = "jdbc:postgresql://b02.seng.engr.uvic.ca/se462g02";
    static final String USERNAME = "se462g02";
    static final String PASSWD = "mvbrioeE";

    //private static Connection conn;

    private Connection conn;

    protected void finalize() throws Throwable {
        closeConnection();
    }

    //****************************
    public void openConnection() throws SQLException
                //****************************
    {
        debugLog.info("Open connection");
        if (conn == null) {

            //verify that postgresql driver is available
            try {
                Class.forName("org.postgresql.Driver");
            } catch (ClassNotFoundException cnfe) {
                //throw new Exception("Class!");
                debugLog.info("Class not found blargh!");
                return;
            }
            try {

                //establish and open a connection to the database
                conn = DriverManager.getConnection("jdbc:postgresql://b02.seng.engr.uvic.ca/se462g02","se462g02","mvbrioeE");//(SERVER + USERNAME + PASSWD);
            } catch (SQLException sqle) {
                debugLog.warn("Open Connection error", sqle);
                throw sqle;
            }
            debugLog.debug("Connection opened");
        }
    }

    //****************************
    public boolean closeConnection() throws SQLException
                //****************************
    {
        try {
            //close the connection
            if (conn != null) {
                debugLog.info("Closing Connection");
                conn.close();
            }
            conn = null;

        } catch (SQLException sqle) {
            debugLog.info("closeConnection error", sqle);
            throw sqle;
            //return false;
        }
        return true;
    }

    //****************************
    public int execUpdate(String qry) throws SQLException
                //****************************
    {
        int result = 0;

        try {
            if (conn == null) {
                debugLog.info("Connection is null");
                return result;
            }

            if (debugLog.isDebugEnabled()) {
                debugLog.debug("Query: " + qry);
            }

            Statement statement = conn.createStatement();
            result = statement.executeUpdate(qry);
            debugLog.info(result + "");
        } catch (SQLException sqle) {
            debugLog.warn("ExecUpdate error", sqle);
            throw sqle;
        }

        return result;
    }


    //****************************
    public ResultSet execQuery(String qry) throws SQLException
    //****************************
    {
        debugLog.info("Running qry in DBConn");
        ResultSet result = null;

        try {
            if (conn == null) {
                debugLog.info("Connection is null");
                return result;
            }

            Statement statement = conn.createStatement();
            result = statement.executeQuery(qry);
            //debugLog.info("qry is:" + qry);
            //debugLog.info("result is: " + result);
            result.first();
            //debugLog.info("first val:" + result.getString(1));
        } catch (SQLException sqle) {
            debugLog.warn("execQuery error", sqle);
            throw sqle;
        }
        debugLog.info("qry complete in DBConn");
        return result;
    }

}
